Amazon Redshift: UDFを使って日付・時刻の文字列をタイムスタンプ型に変換してみる

Amazon Redshiftで良く使いそうな便利系SQL

#ビッグデータ

#Redshift UDF

#Amazon Redshift

#AWS

しんや

2015.12.02

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

データベースに入っている値の『データ型』を変換させたいというケースは、おそらくは大半のプロジェクト・案件・環境で発生し得るものだと思います。

Amazon Redshiftに於いても、その作業の必要性は勿論発生してきます。分かりやすい・ポピュラーな所で言うと『文字列型←→日付(時刻)型』の変換でしょうか。ファイルとしては(というかファイルになっている時点で)文字列型の項目を、日付型(DATE)、日付時刻型(TIMESTAMP)の項目として扱うようなケースです。当エントリではその辺りの作業を行う上で出て来た問題と、問題解決の為に行った対処方法(＝UDFを作成して活用する事で対応した)についてご紹介して行きたいと思います。

Redshiftに於ける『文字列型』←→『日付(時刻)型』変換の状況

当初この課題が出た時は『あ〜あれでしょ、関数一発で済む部分でしょここは』という感じで特に気にも留めていなかったのですが、調べて行くとAmazon Redshiftにおける『文字列型』←→『日付(時刻)型』変換の状況については以下の様なものである事が判明しました。

『日付(時刻)型』から『文字列型』への変換：まぁこちらについては特に悩む事も無く解決。TO_CHAR関数を使って対象日付(時刻)データの内容を好きなように加工出来る。本稿での課題はこちらのものでは無いので以降詳細は割愛。
『文字列型』から『日付(時刻)型』への変換：
- 『文字列型』から『日付型』(DATE型)への変換：
  - データを取り込むタイミングの場合、取り込み先テーブルの対象項目をDATE型に設定、COPYコマンドのDATEFORMATオプションを指定し、任意のフォーマットを併せて定義しておく事で、任意の項目の内容をDATE型の値として取り込む事が出来る。ファイル1カラム＝テーブルカラム1つに対応。(参照：DATEFORMAT / データ変換パラメータ - Amazon Redshift)
  - データベース上で実行する場合、TO_DATE関数を使う事で期待する変換が行える。
- 『文字列型』から『日付時刻型』(TIMESTAMP型)への変換：
  - データを取り込むタイミングの場合、取り込み先テーブルの対象項目をTIMESTAMP型に設定、COPYコマンドのTIMEFORMATオプションを指定し、任意のフォーマットを併せて定義しておく事で、任意の項目の内容をTIMESTAMP型の値として取り込む事が出来る。ファイル1カラム＝テーブルカラム1つに対応。(参考：TIMEFORMAT / データ変換パラメータ - Amazon Redshift)
  - データベース上で実行する場合、対応する関数は存在しない。

問題は赤の太字で強調した部分。文字列から日付時刻型(TIMESTAMP)に変換する術が無いのです。安直に『TO_TIMESTAMP』とかあるのかな〜と思っていましたが、ありませんでした。以下ドキュメントにて、TO_TIMESTAMP()関数は未サポートの旨が記載されています。COPY時のTIMEFORMATオプションも、ファイルの項目が単一の値で『2015/12/01 12:34:56』というような値になっていればこのオプションで対応する事は出来るのですが、日付項目『2015/12/01』と時刻項目『12:34:56』がファイル上で別の項目として存在していた場合、連結して取り込む事は出来ないのです。

サポートされていない PostgreSQL 関数 - Amazon Redshift

UDFを使って必要な関数を自作する

じゃあどうしましょうか？

では、こうしましょう！という事でUDF(User Defined Functions: ユーザー定義の独自関数)を使ってやりたい事を実現したいと思います。UDFの詳細については以下エントリ等をご確認ください。

UDFを作成する際はPythonでコード部分を記載しておく必要があります。いきなり登録する前に、まずはPythonプログラムベースで意図するものが出来るか試してみましょう。

試してみたコードは以下の内容となります。第1引数(日付部分)と第3引数(時刻部分)がそれぞれの項目、それらを連結する際に例えば『2015/12/01 12:34:56』としたいのであれば『半角空白』を、『20151201123456』の様な感じで詰めてしまって良いのであれば『空文字』を設定し、第4引数のフォーマットで変換する値との対応を取る...というものです。第4引数の変換フォーマットについてはPythonの世界でのものを使います。

8.1. datetime — 基本的な日付型および時間型 — Python 2.7ja1 documentation

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
from datetime import datetime

print ""
print "==================================="
print "# 第1引数-日付部分の文字列                      :[" + sys.argv[1] + "]"
print "# 第2引数-日付部分と時刻部分を連結する文字列    :[" + sys.argv[2] + "]"
print "# 第3引数:時刻部分の文字列                      :[" + sys.argv[3] + "]"
print "# 第4引数:日付時刻の変換の際に用いるフォーマット:[" + sys.argv[4] + "]"

print "----"
timestamp_str = sys.argv[1] + sys.argv[2] + sys.argv[3]
print "# 変換対象(String型)"
print "[" + timestamp_str + "]"
print "# 変換結果(Timestamp型):"
print  datetime.strptime(timestamp_str, sys.argv[4])

プログラムを実行してみます。スラッシュやコロンで項目が区切られていればこんな風に、

$ python to_timestamp.python '2015/12/01' ' ' '19:55:23' '%Y/%m/%d %H:%M:%S'

===================================
# 第1引数-日付部分の文字列                      :[2015/12/01]
# 第2引数-日付部分と時刻部分を連結する文字列    :[ ]
# 第3引数:時刻部分の文字列                      :[19:55:23]
# 第4引数:日付時刻の変換の際に用いるフォーマット:[%Y/%m/%d %H:%M:%S]
----
# 変換対象(String型)
[2015/12/01 19:55:23]
# 変換結果(Timestamp型):
2015-12-01 19:55:23

区切り文字もなし、日付と時刻の区切りも無し、とかだとこんな感じです。最後の結果がTIMESTAMP型の値で生成されている事が確認出来ました。

$ python to_timestamp.python '20151201' '' '195523' '%Y%m%d%H%M%S'

===================================
# 第1引数-日付部分の文字列                      :[20151201]
# 第2引数-日付部分と時刻部分を連結する文字列    :[]
# 第3引数:時刻部分の文字列                      :[195523]
# 第4引数:日付時刻の変換の際に用いるフォーマット:[%Y%m%d%H%M%S]
----
# 変換対象(String型)
[20151201195523]
# 変換結果(Timestamp型):
2015-12-01 19:55:23

上記で作成したプログラムの内容を踏まえて、Redshift上で関数を作成してみます。作成にはCREATE FUNCTIONというコマンドを使います。

作成した内容は以下の形となります。4つの文字列型引数を受け取り、返す値はTIMESTAMP型。as $$から$$ language plpythonuまでの間でPythonコードを記述する、という流れになります。

# CREATE FUNCTION cm_to_timestamp (
#   datestr VARCHAR,
#   delimchar VARCHAR,
#   timestr VARCHAR,
#   format VARCHAR)
# returns timestamp
# STABLE
# as $$
# from datetime import datetime
# timestamp_str = datestr + delimchar + timestr
# return datetime.strptime(timestamp_str, format)
# $$ language plpythonu;
CREATE FUNCTION

ちなみに、作成したUDFを削除する場合はDROP FUNCTIONというコマンドを使います。

DROP FUNCTION - Amazon Redshift

# DROP FUNCTION cm_to_timestamp(VARCHAR, VARCHAR, VARCHAR, VARCHAR);
DROP FUNCTION

また、作成したUDFの内容を更新する、上書きする場合はREPLACE FUNCTIONというコマンドを使います。詳細についてはCREATE FUNCTIONと同じページに関連する記載がありますのでご参考頂ければと思います。それぞれ必要に応じて活用してください。

CREATE FUNCTION - Amazon Redshift

作成したUDFを使った動作確認

では実際にUDFを使って処理を試してみましょう。まずは文字列をそのまま渡すスタイル。

# SELECT CM_TO_TIMESTAMP('2015/12/01', ' ', '20:34:56', '%Y/%m/%d %H:%M:%S');
   cm_to_timestamp   
---------------------
 2015-12-01 20:34:56
(1 row)

# SELECT CM_TO_TIMESTAMP('20151231', ' ', '235959', '%Y%m%d %H%M%S');
   cm_to_timestamp   
---------------------
 2015-12-31 23:59:59
(1 row)

次いで既存データの内容を使った変換。まずはテスト用の環境とデータを用意。後者のテーブルは前者のテーブル＋タイムスタンプ項目を定義した内容にしています。

# CREATE TABLE public.udftest (
    id INT NOT NULL,
    datestr CHAR(8) NOT NULL,
    timestr CHAR(6) NOT NULL
  );
  
CREATE TABLE
# INSERT INTO public.udftest VALUES (1, '20151202', '001626');
INSERT 0 1
# INSERT INTO public.udftest VALUES (2, '20151231', '235959');
INSERT 0 1
# INSERT INTO public.udftest VALUES (3, '20160101', '010101');
INSERT 0 1
# SELECT * FROM public.udftest ORDER BY id;
 id | datestr  | timestr 
----+----------+---------
  1 | 20151202 | 001626
  2 | 20151231 | 235959
  3 | 20160101 | 010101
(3 rows)

# CREATE TABLE public.udftest_with_timestamp (
    id INT NOT NULL,
    datestr CHAR(8) NOT NULL,
    timestr CHAR(6) NOT NULL,
    timestamp_value TIMESTAMP
  );
CREATE TABLE

既存の項目を引数に渡した形での実行確認。それぞれちゃんと意図した形でデータをSQL内部で『文字列』から『日付時刻型』データに変換してくれていますね！

# SELECT
    id,
    datestr,
    timestr,
    cm_to_timestamp(datestr, ' ', timestr, '%Y%m%d %H%M%S') AS timestamp_value
  FROM
    public.udftest
  ORDER BY
    id;
 id | datestr  | timestr |   timestamp_value   
----+----------+---------+---------------------
  1 | 20151202 | 001626  | 2015-12-02 00:16:26
  2 | 20151231 | 235959  | 2015-12-31 23:59:59
  3 | 20160101 | 010101  | 2016-01-01 01:01:01
(3 rows)

# INSERT INTO
    public.udftest_with_timestamp
    ( SELECT
        id,
        datestr,
        timestr,
        cm_to_timestamp(datestr, ' ', timestr, '%Y%m%d %H%M%S') AS timestamp_value
      FROM
        public.udftest
      ORDER BY
        id);
        
INSERT 0 3
# SELECT * FROM public.udftest_with_timestamp ORDER BY ID;
 id | datestr  | timestr |   timestamp_value   
----+----------+---------+---------------------
  1 | 20151202 | 001626  | 2015-12-02 00:16:26
  2 | 20151231 | 235959  | 2015-12-31 23:59:59
  3 | 20160101 | 010101  | 2016-01-01 01:01:01
(3 rows)

まとめ

以上、UDFを活用した新たな関数の作成・定義のご紹介でした。やりたい事を『SQLだけで』実現するのは時として困難を伴う事もあるかと思いますが、このような形でPythonのライブラリを活用する事が出来るのであれば使わない手は無いですね！細かいんだけど地味に欲しい...というような時に便利なUDF、皆さんも是非上手く活用していってください。こちらからは以上です。